package com.guagua.chat.topic;
/**
 * 北京八度互联科技有限公司版权所有
 * Copyright (C) Badu Corporation. All Rights Reserved
 */
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.guagua.base.database.ConnectionManager;
import com.guagua.chat.base.BaseDao;
import com.opensymphony.xwork.Result;
/** ******************************************************************
* 类概要:说说 数据库操作类
* @author 李一
* @version 2012-7-2 9:00:00
*******************************************************************/
public class TopicDao extends BaseDao{
	private static Logger log = Logger.getLogger(TopicDao.class);
	private static final String[] _COLUMNS = {"id","room_id","parent_id","user_id","user_nick","content","op_time","create_time"};//列名
	/**
	 * 返回操作表名(t_topic_<房间号末位>)
	 * @param roomId
	 * @return
	 */
	private String getTableName(String roomId){
		return "t_topic_"+roomId.substring(roomId.length()-1);
				
	}
	/**
	 * 新增说说主题或对主题进行评论
	 * @param topicInfo
	 * @param roomId
	 * @return
	 */
	public boolean createTopicOrComment(TopicInfo topicInfo,String roomId){
		String tableName = getTableName(roomId);
		
		Connection con = null;
		PreparedStatement pstmt = null;
		try{
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			con.setAutoCommit(false);
			if(topicInfo.getParent_id()!=0L){//为评论
				String sqlUpd ="update "+tableName+" set op_time="+topicInfo.getCreate_time()+" where id="+topicInfo.getParent_id();
				pstmt = con.prepareStatement(sqlUpd);
				int count = pstmt.executeUpdate();
				if(count !=1){
					return false;
				}
			}
			String sqlColumns = "";
			String valuesColumns ="";
			int columnNo = 0;
			for(int i = 1 ; i < _COLUMNS.length ; i++){
				String colName  =  _COLUMNS[i];
				if("parent_id".equals(colName) && topicInfo.getParent_id()!=0L){
					sqlColumns+=colName+",";
					valuesColumns+="?,";
				}else if("user_id".equals(colName) && topicInfo.getUser_id()!=0L){
					sqlColumns+=colName+",";
					valuesColumns+="?,";
					
				}else if("user_nick".equals(colName) && topicInfo.getUser_nick()!=null){
					sqlColumns+=colName+",";
					valuesColumns+="?,";
				}else if("content".equals(colName) && topicInfo.getContent()!=null){
					sqlColumns+=colName+",";
					valuesColumns+="?,";
				}else if("op_time".equals(colName) && topicInfo.getOp_time()!=0L){
					sqlColumns+=colName+",";
					valuesColumns+="?,";
				}else if("room_id".equals(colName) && topicInfo.getRoom_id()!=0L){
					sqlColumns+=colName+",";
					valuesColumns+="?,";
				}else if("create_time".equals(colName) && topicInfo.getCreate_time()!=0L){
					sqlColumns+=colName+",";
					valuesColumns+="?,";
				}
			}
			sqlColumns = sqlColumns.substring(0,sqlColumns.length()-1);
			valuesColumns = valuesColumns.substring(0,valuesColumns.length()-1);
			String sql = "insert into "+tableName+"("+sqlColumns+") values("+valuesColumns+")";
			log.debug("create topic:"+sql);
			pstmt = con.prepareStatement(sql);
			
			for(int i = 1 ; i < _COLUMNS.length ; i++){
				String colName  =  _COLUMNS[i];
				if("parent_id".equals(colName) && topicInfo.getParent_id()!=0L){
					pstmt.setLong(++columnNo, topicInfo.getParent_id());
				}else if("user_id".equals(colName) && topicInfo.getUser_id()!=0L){
					pstmt.setLong(++columnNo, topicInfo.getUser_id());
					
				}else if("user_nick".equals(colName) && topicInfo.getUser_nick()!=null){
					pstmt.setString(++columnNo, topicInfo.getUser_nick());
				}else if("content".equals(colName) && topicInfo.getContent()!=null){
					pstmt.setString(++columnNo, topicInfo.getContent());
				}else if("op_time".equals(colName) && topicInfo.getOp_time()!=0L){
					pstmt.setLong(++columnNo, topicInfo.getOp_time());
				}else if("room_id".equals(colName) && topicInfo.getRoom_id()!=0L){
					pstmt.setLong(++columnNo, topicInfo.getRoom_id());
					
				}else if("create_time".equals(colName) && topicInfo.getCreate_time()!=0L){
					pstmt.setLong(++columnNo, topicInfo.getCreate_time());
				}
			}
			pstmt.execute();
			con.commit();
			
			return true;
			
		}catch(Exception sqle){
			if(con!=null){
				try {
					con.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			sqle.printStackTrace();
			return false;
		}finally{
			ConnectionManager.close(pstmt, con);
		}
		
	}
	/**
	 * 删除TOPIC
	 * @param id,roomId
	 * @return
	 */
	public boolean delTopicById(long id,String roomId){
		String tableName = getTableName(roomId);
		Connection con = null;
		PreparedStatement pstmt = null;
		try{
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			con.setAutoCommit(false);
			
			String sql ="delete from "+tableName+" where parent_id = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			pstmt.execute();
			sql = "delete from "+tableName+" where id=? and room_id=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			pstmt.setLong(2, Long.parseLong(roomId));
			pstmt.execute();
			con.commit();//成功 提交
			return true;
			
		}catch(Exception sqle){
			log.error(sqle.getMessage(),sqle);
			try {
				con.rollback();
			} catch (SQLException e) {
				log.error(e.getMessage(),e);
			}
			return false;
		}finally{
			ConnectionManager.close(pstmt, con);
		}
	}
	/**
	 * 删除评论
	 * @param id
	 * @param roomId
	 * @return
	 */
	public boolean delCommentById(long id,long parentId,String roomId){
		String tableName = getTableName(roomId);
		Connection con = null;
		PreparedStatement pstmt = null;
		try{
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			String sql = "delete from "+tableName+" where id=? and room_id=? and parent_id=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			pstmt.setLong(2, Long.parseLong(roomId));
			pstmt.setLong(3, parentId);
			pstmt.execute();
			return true;
		}catch(Exception sqle){
			log.error(sqle.getMessage(),sqle);
			return false;
		}finally{
			ConnectionManager.close(pstmt, con);
		}
	}
	
	/**
	 * 查询该房间shuoshuo
	 * @param roomId 房间ID
	 * @param pageNo 页号
	 * @param row 每页显示几行
	 * @return
	 */
	public List<TopicInfo> findTopicsByRoomId(String roomId,int pageNo,int row){
		List<TopicInfo> resultList = null;
		String tableName = getTableName(roomId);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			String selectSQL ="SELECT TOP "+row+" t.* ,(SELECT count(1) FROM "+tableName+" t2 with(nolock)  WHERE t2.parent_id= t.id) 'comment_count' FROM "+tableName+" t with(nolock)  WHERE t.room_id=? AND  t.parent_id IS NULL AND (ID NOT IN (SELECT TOP "+((pageNo-1)*row)+" id FROM "+tableName+" with(nolock)  WHERE room_id=? AND parent_id is null  ORDER BY op_time desc)) ORDER BY  op_time desc";
			pstmt = con.prepareStatement(selectSQL);
			pstmt.setLong(1, Long.parseLong(roomId));
			pstmt.setLong(2, Long.parseLong(roomId));
			rs =pstmt.executeQuery();
			TopicInfo _topicInfo=null;
			while(rs.next()){
				if(resultList == null) resultList = new ArrayList<TopicInfo>();
				_topicInfo = new TopicInfo();
				_topicInfo.setId(rs.getLong("id"));
				_topicInfo.setComment_count(rs.getInt("comment_count"));
				_topicInfo.setContent(rs.getString("content"));
				_topicInfo.setOp_time(rs.getLong("op_time"));
				_topicInfo.setCreate_time(rs.getLong("create_time"));
				_topicInfo.setParent_id(rs.getLong("parent_id"));
				_topicInfo.setRoom_id(rs.getLong("room_id"));
				_topicInfo.setUser_id(rs.getLong("user_id"));
				_topicInfo.setUser_nick(rs.getString("user_nick"));
				resultList.add(_topicInfo);
			}
			log.debug(selectSQL);
			return resultList;
		}catch(Exception sqle){
			log.error(sqle.getMessage(),sqle);
			return resultList;
		}finally{
			ConnectionManager.close(rs,pstmt, con);
		}
	}
	
	/**
	 * 查询shuoshuo主题评论
	 * @param roomId 房间ID
	 * @param topicId 主题ID
	 * @param pageNo 页号
	 * @param row 每页显示数
	 * @return
	 */
	public List<TopicInfo> findTopicCommentsByRoomId(String roomId,long topicId,int pageNo,int row){
		List<TopicInfo> resultList = null;
		String tableName = getTableName(roomId);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			String selectSQL ="SELECT TOP "+row+" t.*  FROM "+tableName+" t with(nolock)  WHERE t.room_id=? AND  t.parent_id=? AND (ID NOT IN (SELECT TOP "+((pageNo-1)*row)+" id FROM "+tableName+" with(nolock)  WHERE room_id=? AND parent_id=?  ORDER BY op_time desc)) ORDER BY  op_time desc";
			pstmt = con.prepareStatement(selectSQL);
			pstmt.setLong(1, Long.parseLong(roomId));
			pstmt.setLong(3, Long.parseLong(roomId));
			
			pstmt.setLong(2, topicId);
			pstmt.setLong(4, topicId);
			rs =pstmt.executeQuery();
			TopicInfo _topicInfo=null;
			while(rs.next()){
				if(resultList == null) resultList = new ArrayList<TopicInfo>();
				_topicInfo = new TopicInfo();
				_topicInfo.setId(rs.getLong("id"));
				_topicInfo.setContent(rs.getString("content"));
				_topicInfo.setOp_time(rs.getLong("op_time"));
				_topicInfo.setCreate_time(rs.getLong("create_time"));
				_topicInfo.setParent_id(rs.getLong("parent_id"));
				_topicInfo.setRoom_id(rs.getLong("room_id"));
				_topicInfo.setUser_id(rs.getLong("user_id"));
				_topicInfo.setUser_nick(rs.getString("user_nick"));
				resultList.add(_topicInfo);
			}
			return resultList;
		}catch(Exception sqle){
			log.error(sqle.getMessage(),sqle);
			return resultList;
		}finally{
			ConnectionManager.close(rs,pstmt, con);
		}
	}
	
	/**
	 * 获取房间所有说说数量
	 * @param roomId
	 * @return
	 */
	public int getTopicCountByRoomId(String roomId)throws Exception{
		String tableName = getTableName(roomId);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			String selectSQL ="SELECT COUNT(1) FROM "+tableName +" with(nolock)  WHERE parent_id is null AND room_id=?";
			pstmt = con.prepareStatement(selectSQL);
			pstmt.setLong(1, Long.parseLong(roomId));
			rs =pstmt.executeQuery();
			TopicInfo _topicInfo=null;
			while(rs.next()){
				 int count = rs.getInt(1);
				 //只显示最新100条
				 if(count >100){
					 count = 100;
				 }
				return count;
			}
			return 0;
		}catch(Exception sqle){
			log.error(sqle.getMessage(),sqle);
			throw sqle;
		}finally{
			ConnectionManager.close(rs,pstmt, con);
		}
	}
	/**
	 * 获取房该主题评论数
	 * @param roomId
	 * @param topicId
	 * @return
	 */
	public int getCommentCountByTopicId(String roomId,long topicId)throws Exception{
		String tableName = getTableName(roomId);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			String selectSQL ="SELECT COUNT(1) FROM "+tableName +" with(nolock)  WHERE parent_id =? AND room_id=?";
			pstmt = con.prepareStatement(selectSQL);
			pstmt.setLong(1, topicId);
			pstmt.setLong(2, Long.parseLong(roomId));
			rs =pstmt.executeQuery();
//			TopicInfo _topicInfo=null;
			while(rs.next()){
				return rs.getInt(1);
			}
			return 0;
		}catch(Exception sqle){
			log.error(sqle.getMessage(),sqle);
			throw sqle;
		}finally{
			ConnectionManager.close(rs,pstmt, con);
		}
	}
	
	
	
}
